<?php
class DataManager {
	

private $idConnexion;
private $idConnexionDB;

/*

private $user="root";
private $mdp="";
private $dbName="meteo";
private $host="localhost";
*/


/*
/////		CONNEXION BASE FREE  

var $idConnexion;
var $idConnexionDB;

/*
var $user="je.tort";            
var $mdp="ja9vinio";            
var $dbName="je_tort";          
var $host="je.tort.sql.free.fr";
*/


/////		CONNEXION BASE NUXBOX   
/* 

private $host="localhost";
private $user="meteostat";            
private $mdp="00jjembF";            
private $dbName="meteostat";          
*/
/*
private $host="mysql4.000webhost.com";
private $user="a9479882_meteo";            
private $mdp="seldon06";            
private $dbName="a9479882_meteo"; 
*/

private $host="localhost";
private $user="root";            
private $mdp="";            
private $dbName="restoweb"; 


private $lang="FR";

private $nom="nom";

/**
	Constructeur
*/
function DataManager() {

		$this->idConnexion=@mysql_connect($this->host,$this->user,$this->mdp);
		$this->idConnexionDB=@mysql_select_db($this->dbName);
}

function verifConnectDB() {
	return ($this->idConnexion && $this->idConnexionDB);
}

function insertResto($nom,$adresse,$codePostal,$ville)
{
	$sql = "INSERT INTO `adresse` (`id`, `nom`,`adresse`,`codePostal`,`ville`)"
	." VALUES (NULL,'$nom','$adresse','$codePostal','$ville')";
	$res=mysql_query($sql);
	return $res;
}

function insertAddressResto($nom,$adresse,$lat,$lng)
{
	$sql = "INSERT INTO `adresse` (`id`, `nom`,`adresse`,`lat`,`lng`)"
		." VALUES (NULL,'$nom','$adresse',$lat,$lng)";
	$res=mysql_query($sql);
	return $res;
}

function getResto($latRight,$latLeft,$lngRight,$lngLeft) {
	$scale = 20;
	/*
	$minlat = round($lat) - $scale;
	$maxlat = round($lat) + $scale;
	$minlng = round($lng) - $scale;
	$maxlng = round($lng) + $scale;
	*/
	$tabRes=array();
	$sql = "SELECT * FROM `adresse` WHERE `lat` BETWEEN $latLeft AND $latRight  AND `lng` BETWEEN $lngLeft AND $lngRight";
	$result=mysql_query($sql);
	$ind=0;
	while ($row = mysql_fetch_assoc($result)) {
		  $tabRes[$ind]['lat']=$row['lat'];
		  $tabRes[$ind]['lng']=$row['lng'];
		  $tabRes[$ind]['data']['id']=$row['id'];
		  $tabRes[$ind]['data']['nom']=$row['nom'];
		  $tabRes[$ind]['data']['adresse']=$row['adresse'];  
		  $ind++;
	}
	return  $tabRes;		
	
	
}


function getVilleByPattern($pattern){
	$nom = $this->nom;
	$sql = "SELECT id,ville from adresse WHERE ville LIKE '$pattern%'"
			." ORDER BY ville";
		
	//echo $sql;	
	$result=mysql_query($sql);
	$tabRes=array();
	$ind=0;
	while ($row = mysql_fetch_assoc($result)) {
		  $tabRes[$ind]['id']=$row['id'];
		  $tabRes[$ind]['ville']=$row['ville'];
		  $ind++;
	}
	return  $tabRes;	
}


function getContinent() {
	$nom = $this->nom;
	$sql = "SELECT id,$nom as nom from continent ORDER BY nom";
	$result=mysql_query($sql);
	$tabRes=array();
	$ind=0;
	while ($row = mysql_fetch_assoc($result)) {
    $tabRes[$ind]['id']=$row['id'];
    $tabRes[$ind]['nom']=$row['nom'];
    $ind++;
	}	
	return $tabRes;	
}

function getPays($idContinent) {
	$nom = $this->nom;
	$sql = "SELECT id,$nom as nom from pays WHERE idContinent=$idContinent ORDER BY $nom";
	$result=mysql_query($sql);
	$tabRes=array();
	$ind=0;
	while ($row = mysql_fetch_assoc($result)) {
		$tabRes[$ind]['id']=$row['id'];
		$tabRes[$ind]['nom']=$row['nom'];
		$ind++;
	}	
	return $tabRes;	
}


function getVille($idPays) {
	$nom = $this->nom;
	$sql = "SELECT id,$nom as nom from ville WHERE idPays=$idPays ORDER BY $nom";
	$result=mysql_query($sql);
	$tabVille=array();
	$ind=0;
	while ($row = mysql_fetch_assoc($result)) {
    $tabVille[$ind]['id']=$row['id'];
    $tabVille[$ind]['nom']=$row['nom'];
    $ind++;
	}
	
	return $tabVille;	
}

function getNomVille($idVille) {
	$nom = $this->nom;
	$sql = "SELECT $nom as nom from ville WHERE id=$idVille";
	$result=mysql_query($sql);
	$row = mysql_fetch_assoc($result); 
  return  $row['nom'];
}
	
	
//verifier la chronologie des dates, cad date fin apres debut

function verifJourAnnee($jourAnnee){
	if($jourAnnee>=1 && $jourAnnee<=366) return true;
	return false;
}

function getDonnee($idVille,$dateDebut,$dateFin) {
	$data=array();
	
	$data['nomVille']=$this->getNomVille($idVille);
	
	
	$debut=$this->getJourAnnee($dateDebut);
	$fin=$this->getJourAnnee($dateFin);
	
	if($this->isSupUnAn($dateDebut,$dateFin)) {
		$debut=1;
		$fin=366;
	}

	try {
	
		if($this->verifJourAnnee($debut) && $this->verifJourAnnee($fin)) {
		
			$this->getTempMinMax($idVille,$data,$debut,$fin);
			$this->getStatMeteo($idVille,$data,$debut,$fin);
			$this->getTempDebutFin($idVille,$data,$debut,$fin);
			//$this->getLeverCoucherSoleil($idVille,$data,$dateDebut,$dateFin);
			$this->getProbaVent($idVille,$data,$debut,$fin);
			$this->getVentMax($idVille,$data,$debut,$fin);
			
		}
	} catch (Exception $e) {
		$this->stockError("debut:$dateDebut fin:$dateFin");
		$data=array();
		
	}
	
	return $data;

}


function getTempMinMax($idVille,&$data,$debut,$fin){
	$sql = "SELECT MAX(TMMediane),MIN(TminMediane)"
	        . " FROM `temperature`"
	        . " WHERE idVille=$idVille"
	        . " AND jour BETWEEN $debut AND $fin";
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 
  $tabTemp=array();
  $data['TMax']=$row['MAX(TMMediane)'];
  $data['TMin']=$row['MIN(TminMediane)'];	
  $sql = "SELECT MAX(TMecartType) "
	        . " FROM `temperature`"
	        . " WHERE"
	        . " jour BETWEEN $debut AND $fin"
	        . " AND idVille=$idVille"
	        . " AND ROUND(TMMediane,1)=".round($data['TMax'],1);	   
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
  $row = mysql_fetch_assoc($result); 
  $data['TMaxET']=$row['MAX(TMecartType)'];
  $sql = "SELECT MAX(TminecartType) "
	        . " FROM `temperature`"
	        . " WHERE"
	        . " jour BETWEEN $debut AND $fin"
	        . " AND idVille=$idVille"
	        . " AND ROUND(TminMediane,1)=".round($data['TMin'],1);
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception($sql);		
	}
  $row = mysql_fetch_assoc($result); 
  $data['TMinET']=$row['MAX(TminecartType)'];  
  $data['TMinSejourInf'] = floor(($data['TMin']-$data['TMinET']));
  $data['TMinSejourSup'] = ceil(($data['TMin']+$data['TMinET']));
  
  $data['TMaxSejourInf'] = floor(($data['TMax']-$data['TMaxET']));
  $data['TMaxSejourSup'] = ceil(($data['TMax']+$data['TMaxET']));
  
  
}

function getTempDebutFin($idVille,&$data,$debut,$fin){
	$sql = "SELECT TMMediane + TMecartType,TMMediane - TMecartType" 
					." FROM `temperature`"
					." WHERE jour=$debut"
					." AND idVille=$idVille";
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 
	$data['TMaxDebutSup']=ceil($row['TMMediane + TMecartType']);
	$data['TMaxDebutInf']=floor($row['TMMediane - TMecartType']);
	$sql = "SELECT TminMediane + TminecartType,TminMediane - TminecartType" 
					." FROM `temperature`"
					." WHERE jour=$debut"
					." AND idVille=$idVille";
	$result=mysql_query($sql);	
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 
	$data['TMinDebutSup']=ceil($row['TminMediane + TminecartType']);
	$data['TMinDebutInf']=floor($row['TminMediane - TminecartType']);		
	$sql = "SELECT TMMediane + TMecartType,TMMediane - TMecartType" 
					." FROM `temperature`"
					." WHERE jour=$fin"
					." AND idVille=$idVille";					
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 
	$data['TMaxFinSup']=ceil($row['TMMediane + TMecartType']);
	$data['TMaxFinInf']=floor($row['TMMediane - TMecartType']);
	$sql = "SELECT TminMediane + TminecartType,TminMediane - TminecartType" 
					." FROM `temperature`"
					." WHERE jour=$fin"
					." AND idVille=$idVille";
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 
	$data['TMinFinSup']=ceil($row['TminMediane + TminecartType']);
	$data['TMinFinInf']=floor($row['TminMediane - TminecartType']);	
	
}


function getStatMeteo($idVille,&$data,$debut,$fin){
	$sql = "SELECT SUM(RAProb),SUM(SNProb),SUM(VVMediane),SUM(PPMediane),"
					." ROUND(AVG(`VVMediane`)-AVG(`VVecartType`)),"
					." ROUND(AVG(`VVMediane`)+AVG(`VVecartType`))" 
					." FROM `donneemeteo`"
					." WHERE jour BETWEEN $debut AND $fin"
					." AND idVille=$idVille";
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 	
	
	$NbJourPluieMed = $row['SUM(RAProb)']/100;
	$data['NbJourPluieMin']=floor($NbJourPluieMed*0.9);	
	$data['NbJourPluieMax']=floor($NbJourPluieMed*1.1)+1;
	$data['VisuMin']=$row['ROUND(AVG(`VVMediane`)-AVG(`VVecartType`))'];	
	if($data['VisuMin']<0) $data['VisuMin']=0;
	$data['VisuMax']=$row['ROUND(AVG(`VVMediane`)+AVG(`VVecartType`))']+1;
	$data['NbJourNeige']=round($row['SUM(SNProb)']/100,0);
	$data['PP']=round($row['SUM(PPMediane)']);
	
	$sql = "SELECT TSProb FROM `donneemeteo`"
					." WHERE jour BETWEEN $debut AND $fin"
					." AND idVille=$idVille";
					
	$tabProbOrage = array();
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}	
	while($row = mysql_fetch_assoc($result)){
		$tabProbOrage[]=1-($row['TSProb']/100);		
	}
	//print_r($tabProbTemp);
	$nbJour = $fin - $debut;
	if($nbJour==0) $nbJour=1;
	
	if($data['NbJourPluieMin']>$nbJour) $data['NbJourPluieMin']=$nbJour;
	if($data['NbJourPluieMax']>$nbJour) $data['NbJourPluieMax']=$nbJour;
	if($data['NbJourNeige']>$nbJour) $data['NbJourNeige']=$nbJour;
	
	$data['ProbOrage'] = round(1-array_product($tabProbOrage),1)*100;

	if($NbJourPluieMed==0) $data['ppjp']=0;
	else $data['ppjp'] = $data['PP'] / $NbJourPluieMed;
	
	$data['pourcentP'] = ($NbJourPluieMed / $nbJour) * 100;
	
}

function getProbaVent($idVille,&$data,$debut,$fin){
	$sql = "SELECT VentProb,TempeteProb FROM `donneemeteo`"
					." WHERE jour BETWEEN $debut AND $fin"
					." AND idVille=$idVille";
					
	$tabProbTemp = $tabProbVent =array();
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}	
	while($row = mysql_fetch_assoc($result)){
		$tabProbTemp[]= 1-($row['TempeteProb']/100);
		$tabProbVent[] = 1-($row['VentProb']/100);		
	}
	$data['TempeteProb'] = round(1-array_product($tabProbTemp),3)*100;
	$data['VentProb'] = round(1-array_product($tabProbVent),3)*100;
	
}


function getVentMax($idVille,&$data,$debut,$fin){
	$sql = "SELECT MAX(VMMediane),MAX(VgMediane)"
	        . " FROM `donneemeteo`"
	        . " WHERE idVille=$idVille"
	        . " AND jour BETWEEN $debut AND $fin";	       
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
	$row = mysql_fetch_assoc($result); 
  $tabTemp=array();
  $data['VMMax']=$row['MAX(VMMediane)'];
	$data['VgMax']=$row['MAX(VgMediane)'];	
  $sql = "SELECT MAX(VMecartType) "
	        . " FROM `donneemeteo`"
	        . " WHERE jour BETWEEN $debut AND $fin"
	        . " AND idVille=$idVille"
	        . " AND ROUND(VMMediane,1)=".$data['VMMax'];	        
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
  $row = mysql_fetch_assoc($result); 
  $data['VMMaxET']=$row['MAX(VMecartType)'];
  $sql = "SELECT MAX(VgecartType) "
	        . " FROM `donneemeteo`"
	        . " WHERE jour BETWEEN $debut AND $fin"
	        . " AND idVille=$idVille"
	        . " AND ROUND(VgMediane,1)=".$data['VgMax'];
	$result=mysql_query($sql);
	if (!$result) {
		$this->stockError($sql);
		throw new Exception('Erreur SQL: '. mysql_error());		
	}
  $row = mysql_fetch_assoc($result); 
  $data['VgMaxET']=$row['MAX(VgecartType)'];  
	
}

function stockError($query){
	$sql = "INSERT INTO error set `query`='$query'";
	mysql_query($sql);
}


function getLeverCoucherSoleil($idVille,&$data,$dateDebut,$dateFin){
	$sql = "SELECT latitude,longitude,altitude FROM ville"
				." WHERE id=$idVille";
	$result=mysql_query($sql);	
	$row = mysql_fetch_assoc($result);
		
	$dateTimeZone = new DateTimeZone("Europe/Paris");
	$dateTime = new DateTime($this->getDateAsStringEN($dateDebut), $dateTimeZone);

	$timeOffset = $dateTimeZone->getOffset($dateTime)/3600;
	$zenith=90+5/6;


	$data['leverDebut']=date_sunrise($this->getTimeStamp($dateDebut),SUNFUNCS_RET_STRING,$row['latitude'],$row['longitude'],$zenith,$timeOffset);
	$data['leverFin']=date_sunrise($this->getTimeStamp($dateFin),SUNFUNCS_RET_STRING,$row['latitude'],$row['longitude'],$zenith,$timeOffset);
		
	$data['coucherDebut']=date_sunset($this->getTimeStamp($dateDebut),SUNFUNCS_RET_STRING,$row['latitude'],$row['longitude'],$zenith,$timeOffset);
	$data['coucherFin']=date_sunset($this->getTimeStamp($dateFin),SUNFUNCS_RET_STRING,$row['latitude'],$row['longitude'],$zenith,$timeOffset);
	
}

function getDateAsStringEN($date) {
	$tabDate=split("/",$date);
	$dateAsString = $tabDate[2]."-".$tabDate[1]."-".$tabDate[0];
	return $dateAsString;
}




function getTimeStamp($date) {
	
		$tabDate=split("/",$date);
		
		$timestamp = mktime(0, 0, 0,(int)$tabDate[1],(int)$tabDate[0],(int)$tabDate[2]);
		if($this->lang=="EN") $timestamp = mktime(0, 0, 0,(int)$tabDate[0],(int)$tabDate[1],(int)$tabDate[2]);
		
		return $timestamp;
	
}


function getJourAnnee($date) {
	
	$tabDate=split("/",$date);
	
	/*
	$numJour=(int)$tabDate[0];

	for($ind=1;$ind < (int)$tabDate[1];$ind++) 	$numJour +=cal_days_in_month(CAL_GREGORIAN, $ind, (int)$tabDate[2]);
	*/
	if($this->lang=="EN") $timestamp = mktime(0, 0, 0,(int)$tabDate[0],(int)$tabDate[1],(int)$tabDate[2]);
	else $timestamp = mktime(0, 0, 0,(int)$tabDate[1],(int)$tabDate[0],(int)$tabDate[2]);
	
	
	//$numJour = getdate($timestamp)['yday'] + 1;
	
	$numJour = idate('z',$timestamp) + 1;
	
	return $numJour;

}

function isSupUnAn($dateDebut,$dateFin) {
	if($this->getPartDate($dateFin,2) > $this->getPartDate($dateDebut,2) +1) {
		return true;
	} 
	
	if($this->getPartDate($dateFin,2) - $this->getPartDate($dateDebut,2) == 1) {
		if($this->getPartDate($dateFin,1) > $this->getPartDate($dateDebut,1))
			return true;
	}

	return false;		
}

function getPartDate($date,$part) {
	
	$tabDate=split("/",$date);
	return (int)$tabDate[$part];
	
}



function setLang($aLang) {
	$this->lang = $aLang;
	switch($aLang) {
		case "EN" : $this->nom="name";break;
		case "ES" : $this->nom="nombre";break;
	}

}

function convertDegreeToFahrenheit($degree) {
	return round(($degree*9/5 + 32),1);
}

function convertKMtoMile($km) {
	return round(($km/1.609344),1);
}

function convertMMtoInch($mm) {
	return round(($mm/25.4),1);
}

}

?>